package edu.tms.dao;

import java.util.List;
import java.util.Map;

import edu.tms.bean.Exam;
import edu.tms.crud.JdbcUtil;
import edu.tms.dao.common.CommnonDao;

/**
 * 考试信息表dao
 * @author Administrator
 *
 */
public class ExamDaoImp implements CommnonDao<Exam>{
	
	/**
	 * 保存内容
	 * @param value
	 * @return 
	 * @throws Exception
	 */
	@Override
	public long save(Exam value) throws Exception {
		String sql="INSERT INTO t_exam(course_name,exam_start_time,exam_end_time,exam_address,exam_supposed_count,";
						sql+= "exam_attendance_count,exam_type_name,create_time,exam_fulfil_state,exam_state)";
						sql+= "VALUE (?,?,?,?,?,?,?,?,?,?);";
		long v = JdbcUtil.insertAuto(sql,
					value.getCourseName(),
					value.getExamStartTime(),
					value.getExamEndTime(),
					value.getExamAddress(),
					value.getExamSupposedCount(),
					value.getExamAttendanceCount(),
					value.getExamTypeName(),
					value.getCreateTime(),
					value.getExamFulfilState(),
					value.getExamState());
		return v;
	}
	/**
	 * 修改内容
	 * @param value
	 * @throws Exception
	 */
	@Override
	public int update(Exam value) throws Exception {
		String sql="UPDATE t_exam SET course_name=?,exam_start_time=?,exam_end_time=?,exam_address=?,exam_supposed_count=?,";
				sql+="exam_attendance_count=?,exam_type_name=?,create_time=?,exam_fulfil_state=?,exam_state=?";
				sql+="WHERE exam_id=?";
		int zhi=JdbcUtil.update(sql,
					value.getCourseName(),
					value.getExamStartTime(),
					value.getExamEndTime(),
					value.getExamAddress(),
					value.getExamSupposedCount(),
					value.getExamAttendanceCount(),
					value.getExamTypeName(),
					value.getCreateTime(),
					value.getExamFulfilState(),
					value.getExamState(),
					value.getExamId());
		return zhi;
	}
	/**
	 * 删除内容
	 * ，成绩表t_grade, 根据考试科目的名字删除
	 * @param value
	 * @throws Exception
	 */
	@Override
	public int delete(Exam value) throws Exception {
		String sql="DELETE t_grade,t_exam FROM t_exam";
			sql+="LEFT JOIN t_grade ON t_grade.exam_id=t_exam.exam_id";
			sql+="WHERE t_exam.course_name=?";
		int zhi=JdbcUtil.update(sql,value.getCourseName());
		return zhi;
	}
	/**
	 * 查找所有的内容
	 * 考试类型表t_exam_type，课程表t_course，成绩表t_grade
	 * @param pageNo 第几页
	 * @param pageSize 每页数字
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Exam> findAll(int pageNo, int pageSize) throws Exception {
	String sql="SELECT te.course_name,te.exam_supposed_count,te.exam_attendance_count,";
		   sql+="SUM(CASE WHEN stu_score >=60 THEN 1 ELSE 0 END) AS 'passCount',";
		   sql+="SUM(CASE WHEN stu_score < 60 THEN 1 ELSE 0 END) AS 'notPasscount',";
		   sql+="AVG(tg.stu_score) AS 'average' FROM t_exam te,t_grade tg WHERE te.`exam_id`=tg.`exam_id` GROUP BY te.`exam_id` limit ?,?";
		return JdbcUtil.query(Exam.class, sql,(pageNo-1)*pageSize,pageSize);
	}
	

	/**
	 * String 对应  数据库 列名（comment)
	 * Object 对应相应列名值
	 * 返回的是所查找的类中的数据。方便EXCEL导出
	 */
	@Override
	public List<Map<String,Object>> getMapList() throws Exception {
		
		return null;
	}
	@SuppressWarnings("unchecked")
	@Override
	public List<Exam> findAll(Object key, int pageNo, int pageSize) throws Exception {
		 String sql ="SELECT * FROM t_exam ";
		 		sql+= "WHERE course_name=? OR exam_start_time=? OR exam_address=? OR exam_type_name=? LIMIT ?,?";
			return JdbcUtil.query(Exam.class, sql,key,key,key,key,(pageNo-1)*pageSize,pageSize);
	}
	@Override
	public Exam findByKey(Object key) throws Exception {
		String sql="select * from t_exam where exam_id=?";
		return (Exam) JdbcUtil.uniqueBean(Exam.class, sql, key);
	}
	
	//成绩统计
	@SuppressWarnings("unchecked")
	public List<Exam> find(String className,String ss) throws Exception{
		String sql="SELECT te.exam_type_name AS examTypeName,te.course_name AS courseName," +
				"te.exam_supposed_count AS examSupposedCount,te.exam_attendance_count AS " +
				"examAttendanceCount, SUM(CASE WHEN stu_score >=60 THEN 1 ELSE 0 END) AS passCount,"
				+ " SUM(CASE WHEN stu_score < 60 THEN 1 ELSE 0 END) AS notPasscount,"
				+ "ROUND(AVG(tg.stu_score),2) AS average  FROM t_exam te,t_grade tg,t_student ts WHERE" 
				+ " te.`exam_id`=tg.`exam_id` AND tg.`stu_id`=ts.`stu_id` AND ts.stu_class_name=? " 
				+ " AND (te.`course_name`=? OR te.exam_type_name=?) GROUP BY te.`exam_id`";
		return JdbcUtil.query(Exam.class, sql,className,ss,ss);
	}

	/**
	 * 查询页数
	 * @param userId
	 * @param examTypeName
	 * @param pageSize
	 * @return
	 * @throws Exception
	 */
	public long queryAllPages(Integer userId, String examTypeName, int pageSize) throws Exception {
		String sql="SELECT COUNT(*) FROM t_exam AS e "
				+ "LEFT JOIN t_grade AS g ON e.exam_id=g.exam_id "
				+ "LEFT JOIN t_student AS s ON s.stu_id=g.stu_id "
				+ "WHERE e.exam_type_name=? AND s.stu_id=?;";
		long page=(long) JdbcUtil.uniqueValue(sql,1,examTypeName,userId);
		return page%pageSize==0?page/pageSize:page/pageSize+1;
	}
	
	/**
	 * 查询数据
	 * @param userId
	 * @param examTypeName
	 * @param pageNo
	 * @param pageSize
	 * @return
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<Exam> queryAllExam(Integer userId, String examTypeName,
			Integer pageNo, int pageSize) throws Exception {
		String sql="SELECT e.exam_type_name,e.course_name,e.exam_start_time,g.stu_score"
				+ " FROM t_exam AS e "
				+ " LEFT JOIN t_grade AS g ON e.exam_id=g.exam_id "
				+ " LEFT JOIN t_student AS s ON s.stu_id=g.stu_id"
				+ " WHERE e.exam_type_name=? AND s.stu_id=? "
				+ " LIMIT ?,?;";
		List<Exam> exam=JdbcUtil.query(Exam.class, sql, examTypeName,userId,(pageNo-1)*pageSize,pageSize);
		return exam;
	}
	
	//查询考试科目下拉列表
	@SuppressWarnings("unchecked")
	public List<Exam> exams() throws Exception{
			String sql="SELECT  DISTINCT course_name FROM t_exam";
			return JdbcUtil.query(Exam.class, sql);
	}
	/**
	 * 查找所有的内容
	 * 考试类型表t_exam_type，课程表t_course，成绩表t_grade
	 * @param pageNo 第几页
	 * @param pageSize 每页数字
	 * @throws Exception
	 */
	@SuppressWarnings("unchecked")
	public List<Exam> findAllByClass(String className,int pageNo, int pageSize) throws Exception {
	String sql="SELECT te.course_name,te.exam_supposed_count,te.exam_attendance_count,";
		   sql+="SUM(CASE WHEN stu_score >=60 THEN 1 ELSE 0 END) AS 'passCount',";
		   sql+="SUM(CASE WHEN stu_score < 60 THEN 1 ELSE 0 END) AS 'notPasscount',";
		   sql+="ROUND(AVG(tg.stu_score),2) AS 'average' FROM t_exam te,t_grade tg,t_student ts WHERE te.`exam_id`=tg.`exam_id` " 
		   	   +" AND tg.`stu_id`=ts.`stu_id` AND ts.`stu_class_name`=? GROUP BY te.`exam_id` limit ?,?";
		return JdbcUtil.query(Exam.class, sql,className,(pageNo-1)*pageSize,pageSize);
	}
	public int totalPage(String className,Integer pageSize){
		String sql="SELECT COUNT(DISTINCT(te.`exam_id`)) AS totalPage FROM t_exam te,t_grade tg ," +
				   "t_student ts WHERE te.`exam_id`=tg.`exam_id` AND tg.`stu_id`=ts.`stu_id`" +
				   " AND ts.`stu_class_name`=?";
		long r;
		try {
			r = (Long) JdbcUtil.uniqueValue(sql,1,className);
			int page=0;
			if(r%pageSize==0){
				page=(int)r/pageSize;
			}else{
				page=(int)r/pageSize+1;
			}
			return page;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
			return 0;
		}
	}
	//查询考试科目下拉列表
	@SuppressWarnings("unchecked")
	public List<Exam> exams(String className) throws Exception{
			String sql="SELECT DISTINCT te.`course_name`FROM t_exam_type ty,t_exam te,t_grade tg,t_student ts " 
					  +" WHERE ty.`exam_type_name`=te.`exam_type_name`AND te.`exam_id`=tg.`exam_id` AND " 
					  +"tg.`stu_id`=ts.`stu_id` AND ts.`stu_class_name`=?"; 
			return JdbcUtil.query(Exam.class, sql,className);
	}
}
